package date.base;

import items.item_data;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbHelper {
	
	private final Context mContext;
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	
	// used to get id of the list in list table
	private static final int ID_COLUMN_INDEX = 0;
	
	//Constants for following created lists
	public static final String LIST_ROWID = "_id";
	public static final String LIST_NAME = "name";
	public static final String KEY_LIST_TABLE_NAME = "table_name";
		
	//Constants needed for items in the list
	public static final String ITEM_NAME="name";
	public static final String ITEM_QUANTITY="quantity";
	public static final String ITEM_VALUE="value";
	public static final String ITEM_TOTAL_ITEM_VALUE="totalvalue";
	public static final String ITEM_ROWID="_id";
	public static final String ITEM_DONE="done";
	
	
	public static final String ITEM_TABLE_NAME = "tablename";
	public static final String DATABASE_NAME="ShoppingManager.db";
	public static final int DATABASE_VERSION=2;
	
	public static final String LIST_TABLE_NAME = "list_table_name";
	
	private static final String CREATE_LIST_TABLE ="create table if not exists " + LIST_TABLE_NAME + " (" + LIST_ROWID + " integer primary key autoincrement, "
    + LIST_NAME + " text not null, " + KEY_LIST_TABLE_NAME + " text not null "  + ");";

	public static final int LIST_NAME_COLUMN = 1;
	public static final int TABLE_NAME_COLUMN = 2;
	
	private static class DatabaseHelper extends SQLiteOpenHelper {
			
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_LIST_TABLE);
							
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(DATABASE_NAME, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS "+DATABASE_NAME);
			onCreate(db);
		}
			
	}
	
	//Constructor
	 public DbHelper (Context context) {
		this.mContext = context;
		
	}
	
	//This method opens data base
	public DbHelper open() throws SQLException {
		mDbHelper = new DatabaseHelper(mContext);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}
	
	
	//This method closes database
	public void close() {
		mDbHelper.close();
	}
	
	
	//Inserts name of the newly created list in the lists table and 
	 public void createList (String list_name) {
		long newPosition = 1;
		Cursor c = getAllLists();
		if (c.getCount()>0) {
			c.moveToLast();
			newPosition = c.getLong(ID_COLUMN_INDEX) + 1;
		}
		ContentValues initialValues = new ContentValues();
		initialValues.put(LIST_NAME, list_name);
		list_name = "ListId_" + newPosition; 		
		initialValues.put(KEY_LIST_TABLE_NAME, list_name);
		
		/* this creates table in the DB for list */
		mDb.execSQL(createSQLStatementAdd(list_name));
		mDb.insert(LIST_TABLE_NAME, null, initialValues);
		c.close();
	}
	
	 public String createList2 (String list_name) {
			long newPosition = 1;
			Cursor c = getAllLists();
			if (c.getCount()>0) {
				c.moveToLast();
				newPosition = c.getLong(ID_COLUMN_INDEX) + 1;
			}
			ContentValues initialValues = new ContentValues();
			initialValues.put(LIST_NAME, list_name);
			list_name = "ListId_" + newPosition; 		
			initialValues.put(KEY_LIST_TABLE_NAME, list_name);
			
			/* this creates table in the DB for list */
			mDb.execSQL(createSQLStatementAdd(list_name));
			mDb.insert(LIST_TABLE_NAME, null, initialValues);
			c.close();
			return list_name;
		}
		 
	 
	public void setListName(long id, String name) {
		ContentValues values = new ContentValues();
		values.put(LIST_NAME, name);
		mDb.update(LIST_TABLE_NAME, values, LIST_ROWID + "=" + id , null);
	}
	
	//Gets all lists from the LIST_TABLE_NAME
	 public Cursor getAllLists() {
		String[] columns = new String[] {LIST_ROWID, LIST_NAME, KEY_LIST_TABLE_NAME};
		Cursor cur = mDb.query(LIST_TABLE_NAME, columns, null, null, null, null, null, null);
		if (cur!=null) {
			cur.moveToFirst();
		}
		return cur;
	}
	 
	 //Creates SQL statement for creating table to be used with
	 public String createSQLStatementAdd (String tableName) {
		return "create table if not exists "+tableName+" (_id integer primary key autoincrement, "
        + DbHelper.ITEM_NAME + " text not null, " + DbHelper.ITEM_VALUE + " double not null, " 
        + DbHelper.ITEM_QUANTITY + " double not null, " + DbHelper.ITEM_TOTAL_ITEM_VALUE + " double not null, " + DbHelper.ITEM_DONE +" boolean not null);";
	}
	
	
	// SQL statement for creating table to be used with execSQL.
	 private String createSQLStatementRemove (String tableName) {
		return "drop table "+tableName;
	}
	
	
	//This method removes all lists from database
	 public int removeAllLists () {
		int counter = 0;
		String[] columns = new String[] {LIST_ROWID, LIST_NAME, KEY_LIST_TABLE_NAME};
		Cursor c = mDb.query(LIST_TABLE_NAME, columns, null, null, null, null, null);
		c.moveToFirst();
		for (c.move(-1); c.moveToNext(); c.isAfterLast()) {
			String tableName = c.getString(TABLE_NAME_COLUMN);
			mDb.execSQL(createSQLStatementRemove(tableName));
			counter++;
		}
		if (counter>0) mDb.delete(LIST_TABLE_NAME, null, null);
		return counter;
	}
	 
	 public Cursor getId(String list_name){
		 String[] columns = new String[] {LIST_ROWID, LIST_NAME, KEY_LIST_TABLE_NAME};
		 Cursor c = mDb.query(true, LIST_TABLE_NAME, columns, LIST_NAME + "=" + list_name, null, null, null, null, null);
		 if (c!=null) {
				c.moveToFirst();
			}
		return c;
	 }
	 
	 public Cursor getList(long id) {
		String[] columns = new String[] {LIST_ROWID, LIST_NAME, KEY_LIST_TABLE_NAME};
		Cursor c = mDb.query(true, LIST_TABLE_NAME, columns, LIST_ROWID + "=" + id, null, null, null, null, null);
		if (c!=null) {
			c.moveToFirst();
		}
		return c;
	}

	public void addItem(item_data itemData) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(ITEM_NAME, itemData.getItemname());
		initialValues.put(ITEM_VALUE, itemData.getPrice());
		initialValues.put(ITEM_QUANTITY, itemData.getQuantity());
		initialValues.put(ITEM_TOTAL_ITEM_VALUE, itemData.getTotalPrice());
		initialValues.put(ITEM_DONE, false);
		String tableN = itemData.getListname();
		mDb.insert(tableN, null, initialValues);
		
	}
	
	public boolean updateDone(String listName, long id, boolean done) {
		ContentValues args= new ContentValues();
		args.put(ITEM_DONE, done);
		return mDb.update(listName, args, ITEM_ROWID+"="+id, null)>0;
	}

	public Cursor getAllItems(String listName) {
		String[] columns = new String[] {ITEM_ROWID, ITEM_NAME, ITEM_VALUE, ITEM_QUANTITY, ITEM_TOTAL_ITEM_VALUE, ITEM_DONE};
		return mDb.query(listName, columns, null, null, null, null, ITEM_NAME );
	}

	public boolean clearList(String listName) {
		return mDb.delete(listName, null, null) > 0;
	}
	
	//changes the status of all the items in the list from true to false
	public boolean changeDone(String listName, long id, boolean done) {
		ContentValues args= new ContentValues();
		args.put(ITEM_DONE, done);
		return mDb.update(listName, args, ITEM_ROWID+"="+id, null)>0;
	}

	
	//removes a single item from the list
	public boolean removeItem(String listName, long id) {
		return mDb.delete(listName, ITEM_ROWID + "=" + id, null) > 0;
	}

	public Cursor getItem(String listName, long id) {
		String[] columns = new String[] {ITEM_ROWID, ITEM_NAME, ITEM_VALUE, ITEM_QUANTITY, ITEM_TOTAL_ITEM_VALUE, ITEM_DONE};
		Cursor c = mDb.query(true, listName, columns, ITEM_ROWID + "=" + id, null, null, null, null, null);
		if (c!=null) {
			c.moveToFirst();
		}
		return c;
	}

	public boolean updateItem(item_data itemData) {
		ContentValues initialValues= new ContentValues();
		initialValues.put(ITEM_NAME, itemData.getItemname());
		initialValues.put(ITEM_VALUE, itemData.getPrice());
		initialValues.put(ITEM_QUANTITY, itemData.getQuantity());
		initialValues.put(ITEM_TOTAL_ITEM_VALUE, itemData.getTotalPrice());
		initialValues.put(ITEM_DONE, false);
		int id = (int) itemData.getRowId();
		return mDb.update(itemData.getListname(), initialValues, ITEM_ROWID+"="+id, null)>0;
	}

	public long removeList (long id) {
		String[] columns = new String[] {LIST_ROWID, LIST_NAME, KEY_LIST_TABLE_NAME};
		Cursor c = mDb.query(true, LIST_TABLE_NAME, columns, LIST_ROWID + "=" + id, null, null, null, null, null);
		if (c!=null) {
			c.moveToFirst();
		}
		String tableName = c.getString(TABLE_NAME_COLUMN);
		mDb.execSQL(createSQLStatementRemove(tableName));
		return mDb.delete(LIST_TABLE_NAME, LIST_ROWID + "=" + id, null);
		
	}


}
